package cdu.zzq.dao.impl;

import cdu.zzq.dao.UserDao;
import cdu.zzq.model.User;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public User get(int id) {
        User user = null;
        String sql = "SELECT * FROM user WHERE id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs= pstmt.executeQuery();
            while (rs.next()) {
                user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setSex(rs.getString("sex"));
                user.setHobby(rs.getString("hobby"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    @Override
    public List<User> findAll() {
        List<User> userList = new ArrayList<User>();
        String sql = "SELECT * FROM user";
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setPassword(rs.getString("password"));
                user.setSex(rs.getString("sex"));
                user.setHobby(rs.getString("hobby"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public List<User> findByPage(int start, int num) {
        List<User> userList = new ArrayList<User>();
        String sql = "SELECT * FROM user ORDER BY id DESC LIMIT ?,?";
        try {
            pstmt = conn. prepareStatement(sql);
            pstmt. setInt(1, start);
            pstmt. setInt(2, num);
            rs = pstmt. executeQuery( );
            while (rs.next()) {
                User user = new User( );
                user.setId(rs. getInt("id"));
                user.setName( rs . getString("name"));
                user.setPassword(rs. getString("password"));
                user.setSex(rs. getString("sex"));
                user.setHobby( rs . getString( "hobby"));
                userList.add(user);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return userList;
    }

    @Override
    public int insert(User user) {
        int rows = 0;
        String sql = "INSERT INTO user(name,password,sex,hobby) VALUES (?,?,?,?)";
        try {
            pstmt = conn. prepareStatement(sql);
            pstmt. setString(1, user.getName());
            pstmt. setString(2, user.getPassword());
            pstmt. setString(3, user.getSex());
            pstmt. setString(4, user.getHobby());
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return rows ;
    }

    @Override
    public int update(User user) {
        int rows = 0;
        String sql = "UPDATE user SET name=? , password=? , sex=? , hobby=? WHERE id=?";
        try {
            pstmt = conn. prepareStatement(sql);
            pstmt. setString(1, user.getName());
            pstmt. setString(2, user.getPassword());
            pstmt. setString(3, user.getSex());
            pstmt. setString(4, user.getHobby());
            pstmt. setInt(5, user.getId());
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e. printStackTrace( );
        }
        return rows;
    }

    @Override
    public int delete(int id) {
        int rows = 0;
        String sql = "DELETE FROM user WHERE id=?";
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt. setInt(1, id);
            rows = pstmt.executeUpdate( );
        } catch (SQLException e) {
            e. printStackTrace();
        }
        return rows ;
    }

    @Override
    public int count() {
        int count = 0;
        String sql = "SELECT count(*) from user";
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}
